const JSDOM = require('jsdom').JSDOM;
const fs = require('fs');
const Mysql = require('mysql-pro');

function html2$(html) {
    let document = new JSDOM(html).window.document;
    return document.querySelectorAll.bind(document);
}

let db = new Mysql({
    mysql: {
        host: 'localhost',
        port: 3306,
        user: 'root',
        password: 'root',
        database: 'shouji_tmall',
    }
})

async function query(sql) {
    await db.startTransaction();
    let data = await db.executeTransaction(sql);
    await db.stopTransaction();
    return data;
}

fs.readFile('tmp/hot_shouji_tmall.html', (err, buffer) => {
    if (err) {
        console.log('读取失败');
    } else {
        let oLis = html2$(buffer.toString())('li');
        let datas = Array.from(oLis).map((li) => {
            let oA = li.getElementsByClassName('mod-g-photo')[0];
            return {
                url: 'https:' + oA.href,
                img_src: 'https:' + oA.children[0].getAttribute('data-lazyload-src'),
                name: li.getElementsByClassName('mod-g-tit')[0].children[0].innerHTML,
                descrption: li.getElementsByClassName('mod-g-desc')[0].innerHTML,
                price: li.getElementsByClassName('mod-g-nprice')[0].innerHTML.match(/\d+(\.\d)?/g)[0],
                sales: li.getElementsByClassName('mod-g-sales')[0].innerHTML.match(/\d+/g)[0],
            }
        });
        console.log(datas);

        (async function() {
            for (let i = 0, l = datas.length; i < l; i++) {
                // 查询item_table表中有没有这个url
                // console.log(datas[i]);
                let rows = await query(`SELECT * FROM item_table WHERE url='${datas[i].url}'`);
                // 如果没有就插入  如果有就更新
                if (rows.length > 0) {
                    await query(`UPDATE item_table SET img_src='${datas[i].img_src}', name='${datas[i].name}', descrption='${datas[i].descrption}', price='${datas[i].price}', sales='${datas[i].sales}' WHERE ID=${rows[0].ID}`);
                } else {
                    await query(`INSERT INTO item_table (ID, url, img_src, name, descrption, price, sales) VALUES(${i}, '${datas[i].url}', '${datas[i].img_src}', '${datas[i].name}', '${datas[i].descrption}', '${datas[i].price}', '${datas[i].sales}')`);
                }
            }
        })()

    }
})
